package team.sun.sss.controller.staff;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import team.sun.sss.dao.staff.StaffDao;
import team.sun.sss.pojo.staff.Staff;
import team.sun.sss.service.staff.StaffService;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

@Controller
public class StaffDownLoadController {
    @Autowired
    private StaffDao staffDao;
    @Autowired
    private StaffService staffService;

    @RequestMapping("/upload")
    @ResponseBody
    public String excelImport(@RequestParam(value = "filename") MultipartFile file, HttpSession session){
        String fileName = file.getOriginalFilename();
        int result = 0;

        try {
            result = staffService.addToDatabase(file);
        } catch (Exception e){
            e.printStackTrace();
        }
        if (result > 0){
            return "恭喜，账单导入成功！";
        }else {
            return "害！导入失败了...";
        }
    }

    @RequestMapping(value = "/downloadsExcelDown", method = RequestMethod.GET)
    public void downloadsExcelDown(HttpServletResponse response) throws IOException {
        List<Staff> bgmExcelDownloads = staffDao.getStaffList();
        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet("政工信息数据");

        HSSFRow row = null;

        row = sheet.createRow(0);//创建第一个单元格
        row.setHeight((short) (26.25 * 20));
        row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值

        /*为标题设计空间
         * firstRow从第1行开始
         * lastRow从第0行结束
         *
         *从第1个单元格开始
         * 从第3个单元格结束
         */
        CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 2);
        sheet.addMergedRegion(rowRegion);

      /*CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);
      sheet.addMergedRegion(columnRegion);*/

        row = sheet.createRow(1);
        row.setHeight((short) (22.50 * 20));//设置行高
        row.createCell(0).setCellValue("职工号");//为第一个单元格设值
        row.createCell(1).setCellValue("姓名");//为第二个单元格设值
        row.createCell(2).setCellValue("邮箱");//为第三个单元格设值
        row.createCell(3).setCellValue("性别");//为第四个单元格设值
        row.createCell(4).setCellValue("民族");//为第四个单元格设值

        row.createCell(5).setCellValue("所在院系");
        row.createCell(6).setCellValue("籍贯");
        row.createCell(7).setCellValue("政治面貌");
        row.createCell(8).setCellValue("聘任级别");
        row.createCell(9).setCellValue("学历");
        row.createCell(10).setCellValue("身份证");
        row.createCell(11).setCellValue("学位");
        row.createCell(12).setCellValue("出生日期");
        row.createCell(13).setCellValue("职务");
        row.createCell(14).setCellValue("婚姻");
        row.createCell(15).setCellValue("参加工作时间");
        row.createCell(16).setCellValue("职称");
        row.createCell(17).setCellValue("手机");
        row.createCell(18).setCellValue("邮政编码");
        row.createCell(19).setCellValue("在职状态");
        row.createCell(20).setCellValue("担任辅导员时间");
        row.createCell(21).setCellValue("微信");
        row.createCell(22).setCellValue("QQ");
        //遍历所获取的数据
        for (int i = 0; i < bgmExcelDownloads.size(); i++) {
            row = sheet.createRow(i + 2);
            Staff staff = bgmExcelDownloads.get(i);
            row.createCell(0).setCellValue(staff.getStaNum());
            row.createCell(1).setCellValue(staff.getStaName());
            row.createCell(2).setCellValue(staff.getStaEmail());
            row.createCell(3).setCellValue(staff.isStaSex()?"男":"女");
            row.createCell(4).setCellValue(staff.isStaSex()?"汉族":"其他");

            row.createCell(5).setCellValue(staff.getStaCollege());
            row.createCell(6).setCellValue(staff.getStaProvince());
            row.createCell(7).setCellValue(staff.getStaPoliticalOutlook());
            row.createCell(8).setCellValue(staff.getStaSort());
            row.createCell(9).setCellValue(staff.getStaEducationBackground());
            row.createCell(10).setCellValue(staff.getStaIdCard());
            row.createCell(11).setCellValue(staff.getStaDegree());
            row.createCell(12).setCellValue(String.valueOf(staff.getStaBirthday()));
            row.createCell(13).setCellValue(staff.getStaWorkType());
            row.createCell(14).setCellValue(staff.isStaMarriage()?"已婚":"未婚");
            row.createCell(15).setCellValue(String.valueOf(staff.getStaFirstWorkDate()));
            row.createCell(16).setCellValue(staff.getStaWorkTitle());
            row.createCell(17).setCellValue(staff.getStaPhoneNum());
            row.createCell(18).setCellValue(staff.getStaPostalCode());
            row.createCell(19).setCellValue(staff.isStaWorkState()?"在职":"离职");
            row.createCell(20).setCellValue(staff.getStaCounsellorTime());
            row.createCell(21).setCellValue(staff.getStaWechat());
            row.createCell(22).setCellValue(String.valueOf(staff.getStaQqNum()));
        }
        sheet.setDefaultRowHeight((short) (16.5 * 20));
        //列宽自适应
        for (int i = 0; i <= 13; i++) {
            sheet.autoSizeColumn(i);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        OutputStream os = response.getOutputStream();
        response.setHeader("Content-disposition", "attachment;filename=StaffData.xls");//默认Excel名称
        wb.write(os);
        os.flush();
        os.close();
    }
}
